package mtyan;
import java.io.*;
import java.util.*;
import java.sql.*;

public class MySQLConnector {
	
	public String login;
	public String password;
	public Connection conn;
	
	public MySQLConnector(){
		getProperties();
		prepareDataBase();
	}
	
	public void getProperties(){
		try {
			Properties prop = new Properties();
			File f = new File("../Workspace/Test4_Web/config.properties");
			String s = f.getAbsolutePath();
			prop.load(new FileInputStream(s));
			login = prop.getProperty("login");
			password = prop.getProperty("password");
		} catch (IOException e){
			System.out.println("Extracting of properties failed.");
			e.printStackTrace();
		}
	}
	
	public void openConnection(){
		try{
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost/TFC",login,password);
		} catch (SQLException e){
			System.out.println("Connection opening failed.");
			e.printStackTrace();
		} catch (ClassNotFoundException e){
			System.out.println("Class \"com.mysql.jdbc.Driver\" wasn't found.");
			e.printStackTrace();
		}
	}
	
	public void closeConnection(){
		try{
			conn.close();
		} catch (SQLException e){
			e.printStackTrace();
		}
	}
	
	public void prepareDataBase(){
		openConnection();
		try{
			Statement statement = conn.createStatement();
			String createTable = "CREATE TABLE IF NOT EXISTS User (" +
					"id MEDIUMINT NOT NULL AUTO_INCREMENT," +
					"firstname CHAR(30)," +
					"lastname CHAR(30)," +
					"login CHAR(10)," +
					"password CHAR(10)," +
					"id_role TINYINT," +
					"birthdate DATE," +
					"PRIMARY KEY (id))";
			statement.executeUpdate(createTable);
			createTable = "CREATE TABLE IF NOT EXISTS Event (" +
					"id MEDIUMINT NOT NULL AUTO_INCREMENT," +
					"subject CHAR(100)," +
					"description TEXT(100)," +
					"begin DATETIME," +
					"end DATETIME," +
					"PRIMARY KEY (id))";
			statement.executeUpdate(createTable);
			createTable = "CREATE TABLE IF NOT EXISTS Event_User (" +
					"id_event MEDIUMINT," +
					"id_user MEDIUMINT)";
			statement.executeUpdate(createTable);
			createTable = "CREATE TABLE IF NOT EXISTS Comment (" +
					"id MEDIUMINT NOT NULL AUTO_INCREMENT," +
					"id_event MEDIUMINT," +
					"comment TEXT(100)," +
					"PRIMARY KEY (id))," +
					"FOREIGN KEY (id_event) REFERENCES event(id) ON DELETE CASCADE";
			statement.executeUpdate(createTable);
			/*createTable = "CREATE TABLE IF NOT EXISTS Role (" +
					"id MEDIUMINT)";
			statement.executeUpdate(createTable);*/
			/*createTable = "CREATE TABLE IF NOT EXISTS Images (" +
					"id MEDIUMINT NOT NULL AUTO_INCREMENT," +
					"file BLOB)";
			statement.executeUpdate(createTable);*/
		} catch(SQLException e){
			System.out.println("DataBase preparation failed.");
			e.printStackTrace();
		}
		closeConnection();
	}
	
	public List<List<String>> executeQuery(String select, int recordSize, String from, String where){
		openConnection();
		List<List<String>> result = new ArrayList<List<String>>();
		try {
			Statement statement = conn.createStatement();
			String query = "SELECT "+select+" FROM "+from;
			if(where != null){
				query += " WHERE " + where;
			}
			ResultSet resultSet = statement.executeQuery(query);
			while(resultSet.next()){
				List<String> row = new ArrayList<String>();
				for(int i=1;i<=recordSize;i++){
					row.add(resultSet.getString(i));
				}
				result.add(row);
			}
		} catch (SQLException e) {
			System.out.println("Query execution failed.");
			e.printStackTrace();
		}
		closeConnection();
		return result;
	}
	
	public String executeSimpleQuery(String select, String from, String append){
		openConnection();
		String result = "";
		try {
			Statement statement = conn.createStatement();
			String query = "SELECT "+select+" FROM "+from;
			if(append != null){
				query += " " + append;
			}
			ResultSet resultSet = statement.executeQuery(query);
			if(resultSet.next()){
				result = resultSet.getString(1);
			}
		} catch (SQLException e) {
			System.out.println("Query execution failed.");
			e.printStackTrace();
		}
		closeConnection();
		return result;
	}
	
	public void executeUpdate(String query){
		openConnection();
		try {
			Statement statement = conn.createStatement();
			statement.executeUpdate(query);
		} catch (SQLException e) {
			System.out.println("Update execution failed.");
			e.printStackTrace();
		}
		closeConnection();
	}
	
	public Object pickupObject(String objectClass, int objectId){
		if (objectClass.equals("Event")){
			List<String> eventFields = executeQuery("*",5,"event","id="+objectId).get(0);
			Event event = new Event();
			event.setId(objectId);
			event.setSubject(eventFields.get(1));
			event.setDescription(eventFields.get(2));
			event.setBegin(eventFields.get(3));
			event.setEnd(eventFields.get(4));
			return event;
			/*add participants*/
		}
		return null;
	}
	
	public String getLogin() {
		return login;
	}

	public void setLogin(String login) {
		this.login = login;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}
	
	
}
